package sql.connection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import jenamuseum.annotation.*;

public class DBConnector {
	
	private  Connection connect = null;
	private  Statement statement = null;
	private  PreparedStatement preparedStatement = null;
	private  ResultSet resultSet = null;
	private static final String  mySQL = "jdbc:mysql://localhost/Annotation?";
	private static final String user = "root";
	private static final String password="admin";


	public DBConnector()
	{
		super();
	}
	
	public void getConnection()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver");
		// Setup the connection with the DB
		connect = DriverManager
				.getConnection(mySQL + "user=" + user + "&password=" + password);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println(e.toString());
		}
	}
	
	public void insertAnnotation(Annotation annotation) throws SQLException
	{
		getConnection();
		preparedStatement = connect.prepareStatement("insert into  Annotation.ANNOTATION (ARTWORK,TITLE,BODY,AUTHOR,DATE) values (?, ?, ?, ?,CURRENT_TIMESTAMP)");
		preparedStatement.setString(1, annotation.artwork);
		preparedStatement.setString(2, annotation.title);
		preparedStatement.setString(3, annotation.body);
		preparedStatement.setString(4, annotation.author);
		preparedStatement.executeUpdate();
		close();
	}
	
	public void updateAnnotation(Annotation annotation) throws SQLException
	{
		getConnection();
		preparedStatement = connect.prepareStatement("update Annotation.ANNOTATION SET TITLE=?,BODY=?,AUTHOR=? " +
				"  where ANNOTATION_ID= "+ annotation.id+  " ");
		preparedStatement.setString(1, annotation.title);
		preparedStatement.setString(2, annotation.body);
		preparedStatement.setString(3, annotation.author);
		preparedStatement.executeUpdate();
		close();
	}

	
	public AnnotationList getAllAnotation() throws SQLException
	{
		AnnotationList result = new AnnotationList();
		getConnection();
		preparedStatement = connect
		.prepareStatement("SELECT ANNOTATION_ID, ARTWORK_NAME, TITLE, BODY, AUTHOR, DATE from Annotation.ANNOTATION INNER JOIN Annotation.ARTWORK WHERE ANNOTATION.ARTWORK=ARTWORK.ARTWORK_ID ORDER BY ANNOTATION_ID ");
		resultSet = preparedStatement.executeQuery();
		while(resultSet.next())
		{
			int curr_id = resultSet.getInt("ANNOTATION_ID");
			String curr_artwork = resultSet.getString("ARTWORK_NAME");
			String curr_author=resultSet.getString("AUTHOR");
			String curr_title=resultSet.getString("TITLE");
			String curr_body = resultSet.getString("BODY");
			String curr_date = resultSet.getTimestamp("DATE").toString();
		//	System.out.println(curr_date);
			result.add(new Annotation(curr_id, curr_title, curr_artwork,curr_body, curr_author, curr_date));
		}
//writeResultSet(resultSet);
		close();
		return result;
	}

	
	
	public AnnotationList getAllAnotationByKeyword(String keyword) throws SQLException
	{
		System.out.println("Keyword for search is "+ keyword);
		AnnotationList result = new AnnotationList();
		getConnection();
		preparedStatement = connect
		.prepareStatement("SELECT ANNOTATION_ID, ARTWORK_NAME, TITLE, BODY, AUTHOR, DATE from Annotation.ANNOTATION INNER JOIN Annotation.ARTWORK ON(ANNOTATION.ARTWORK=ARTWORK.ARTWORK_ID) WHERE AUTHOR LIKE '%"+ keyword +"%' " +
				" OR TITLE LIKE '%"+ keyword +"%' or BODY LIKE '%"+keyword+"%' ORDER BY ANNOTATION_ID ");
		resultSet = preparedStatement.executeQuery();
		while(resultSet.next())
		{
			int curr_id = resultSet.getInt("ANNOTATION_ID");
			String curr_artwork = resultSet.getString("ARTWORK_NAME");
			String curr_author=resultSet.getString("AUTHOR");
			String curr_title=resultSet.getString("TITLE");
			String curr_body = resultSet.getString("BODY");
			String curr_date = resultSet.getTimestamp("DATE").toString();
		//	System.out.println(curr_date);
			result.add(new Annotation(curr_id, curr_title, curr_artwork,curr_body, curr_author, curr_date));
		}
//writeResultSet(resultSet);
		close();
		return result;
	}

	
	
	public void deleteAnnotation(int id) throws SQLException
	{
		getConnection();
		preparedStatement = connect
		.prepareStatement("DELETE FROM Annotation.ANNOTATION WHERE ANNOTATION_ID=" + id +  ";");
		preparedStatement.executeUpdate();
		close();
	}
	
	public AnnotationList getAllAnotation(String artwork) throws SQLException
	{
		AnnotationList result = new AnnotationList();
		getConnection();
		preparedStatement = connect
		.prepareStatement("SELECT ARTWORK, TITLE, BODY, AUTHOR from Annotation.ANNOTATION WHERE ARTWORK='"+artwork + "' ");
		resultSet = preparedStatement.executeQuery();
		while(resultSet.next())
		{
			String curr_artwork = resultSet.getString("ARTWORK");
			String curr_author=resultSet.getString("AUTHOR");
			String curr_title=resultSet.getString("TITLE");
			String curr_body = resultSet.getString("BODY");
			String curr_date = resultSet.getString("DATE");
			
			result.add(new Annotation(0, curr_title, curr_artwork,curr_body, curr_author, curr_date));
		}
//writeResultSet(resultSet);
		close();
		return result;
	}
	
	public Annotation getOneAnnotation(int id)
	{
		getConnection();
		try {
			preparedStatement = connect
			.prepareStatement("SELECT ARTWORK, TITLE, BODY, AUTHOR from Annotation.ANNOTATION WHERE ANNOTATION_ID="+id + " ");
			// TODO Auto-generated catch block
		resultSet = preparedStatement.executeQuery();
//writeResultSet(resultSet);

resultSet.next();
	String curr_artwork = resultSet.getString("ARTWORK");
	String curr_author=resultSet.getString("AUTHOR");
	String curr_title=resultSet.getString("TITLE");
	String curr_body = resultSet.getString("BODY");
	String curr_date = resultSet.getString("DATE");

	close();
	return new Annotation(0, curr_title, curr_artwork,curr_body, curr_author, curr_date);
	} catch (SQLException e) {
		e.printStackTrace();
		return null;
	}

	}

	
	private  void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {	
		e.printStackTrace();


		}
	}
	
	
	
	private void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		while (resultSet.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);
			String user = resultSet.getString("ARTWORK");
			String body = resultSet.getString("BODY");
			String artwork = resultSet.getString("AUTHOR");
			System.out.println("User: " + user);
			System.out.println("Body: " + body);
			System.out.println("Artwork: " + artwork);
		}
	}

}
